﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.Odbc;

//using Microsoft.Office.Interop.Excel;

namespace Corsat.DataMigrationTool
{
    class Program
    {
        /*
        static void Main(string[] args)
        {
            SqlConnection connection_old = new SqlConnection("Password=cWSa0711;User ID=corsat_dblogin;Initial Catalog=corsat_db;Data Source=corsat-youthbishopric.com;");

            SqlCommand command_old = new SqlCommand("", connection_old);

            SqlConnection connection_new = new SqlConnection("SERVER=74.86.115.80; Database=jmy_test; integrated security=false; user id=jmy_test; password=CorsatAdmin@123; Timeout = 500;");
            SqlCommand command_new = new SqlCommand("", connection_new);

            //try
            //{
                

                #region Courses Matching Table
                List<MatchItem> CoursesMatchingTable = new List<MatchItem>();
                CoursesMatchingTable.Add(new MatchItem(22, 1));
                CoursesMatchingTable.Add(new MatchItem(24, 2));
                CoursesMatchingTable.Add(new MatchItem(17, 3));
                CoursesMatchingTable.Add(new MatchItem(52, 4));
                CoursesMatchingTable.Add(new MatchItem(5, 5));
                CoursesMatchingTable.Add(new MatchItem(1, 6));
                CoursesMatchingTable.Add(new MatchItem(2, 7));
                CoursesMatchingTable.Add(new MatchItem(36, 8));
                CoursesMatchingTable.Add(new MatchItem(37, 9));
                CoursesMatchingTable.Add(new MatchItem(64, 10));
                CoursesMatchingTable.Add(new MatchItem(32, 11));
                CoursesMatchingTable.Add(new MatchItem(33, 12));
                CoursesMatchingTable.Add(new MatchItem(9, 13));
                CoursesMatchingTable.Add(new MatchItem(10, 14));
                CoursesMatchingTable.Add(new MatchItem(13, 15));
                CoursesMatchingTable.Add(new MatchItem(14, 16));
                CoursesMatchingTable.Add(new MatchItem(56, 17));
                CoursesMatchingTable.Add(new MatchItem(40, 18));
                CoursesMatchingTable.Add(new MatchItem(41, 19));
                CoursesMatchingTable.Add(new MatchItem(27, 20));
                CoursesMatchingTable.Add(new MatchItem(28, 21));
                CoursesMatchingTable.Add(new MatchItem(44, 22));
                CoursesMatchingTable.Add(new MatchItem(48, 23));
                #endregion

                #region Grades Matching Table

                List<MatchItem> GradesMatchingTable = new List<MatchItem>();
                GradesMatchingTable.Add(new MatchItem(374, 310));
                GradesMatchingTable.Add(new MatchItem(375, 311));
                GradesMatchingTable.Add(new MatchItem(376, 312));
                GradesMatchingTable.Add(new MatchItem(377, 313));
                GradesMatchingTable.Add(new MatchItem(378, 314));
                GradesMatchingTable.Add(new MatchItem(379, 315));
                GradesMatchingTable.Add(new MatchItem(380, 316));
                GradesMatchingTable.Add(new MatchItem(381, 317));

                #endregion

                command_old.CommandText =
    @"
select * from Accounts Where IsActivcated = 'true'
select * from AccountTelephones

SELECT 
  AccountID
, CourseId
, Degree
, GradeId
, AccountIdInCourse
 
 FROM AccountCourses 
";
                SqlDataAdapter adapter_old = new SqlDataAdapter();
                adapter_old.SelectCommand = command_old;

                DataSet ds_old = new DataSet();

                adapter_old.Fill(ds_old);

                List<Account> Accounts = new List<Account>();

                connection_new.Open();

                for (int i = 750; i < ds_old.Tables[0].Rows.Count; i++)
                {
                    Account temp = new Account();

                    DataRow drAccount_old = ds_old.Tables[0].Rows[i];
                    // Account Corsat Number used to be the old AccountId itself.
                    temp.AccountCorsatId = drAccount_old["AccountId"].ToString();

                    // SSN
                    temp.SSN = drAccount_old["SSN"].ToString();

                    //FullName
                    temp.FullName = drAccount_old["FirstName"].ToString();
                    Console.WriteLine("Processing : " + (i + 1).ToString() + " FROM " + (ds_old.Tables[0].Rows.Count.ToString()) + " - AccountId = " + temp.AccountCorsatId);

                    // Birthdate
                    temp.BirthDate = Convert.ToDateTime(drAccount_old["BirthDate"]);

                    // Is Activated
                    temp.IsActivated = Convert.ToBoolean(drAccount_old["IsActivcated"]);

                    // Telephones
                    DataRow[] drTelephones = ds_old.Tables[1].Select("AccountId = " + temp.AccountCorsatId);

                    temp.Telephones = new List<Telephone>();

                    if (drTelephones.Length > 0)
                    {


                        for (int j = 0; j < drTelephones.Length; j++)
                        {
                            Telephone tele = new Telephone();

                            string number = drTelephones[j]["Number"].ToString();
                            if (number.Length == 9)
                            {

                                if (number.StartsWith("12")) { number = "0122" + number.Remove(0, 2); tele.TeleType = 30; }
                                else if (number.StartsWith("17")) { number = "0127" + number.Remove(0, 2); tele.TeleType = 30; }
                                else if (number.StartsWith("18")) { number = "0128" + number.Remove(0, 2); tele.TeleType = 30; }
                                else if (number.StartsWith("10")) { number = "0100" + number.Remove(0, 2); tele.TeleType = 30; }
                                else if (number.StartsWith("16")) { number = "0106" + number.Remove(0, 2); tele.TeleType = 30; }
                                else if (number.StartsWith("19")) { number = "0109" + number.Remove(0, 2); tele.TeleType = 30; }
                                else if (number.StartsWith("11")) { number = "0111" + number.Remove(0, 2); tele.TeleType = 30; }
                                else if (number.StartsWith("14")) { number = "0114" + number.Remove(0, 2); tele.TeleType = 30; }
                            }
                            else
                                if (number.Length == 10)
                                {
                                    if (number.StartsWith("012")) { number = "0122" + number.Remove(0, 3); tele.TeleType = 30; }
                                    else if (number.StartsWith("017")) { number = "0127" + number.Remove(0, 3); tele.TeleType = 30; }
                                    else if (number.StartsWith("018")) { number = "0128" + number.Remove(0, 3); tele.TeleType = 30; }
                                    else if (number.StartsWith("010")) { number = "0100" + number.Remove(0, 3); tele.TeleType = 30; }
                                    else if (number.StartsWith("016")) { number = "0106" + number.Remove(0, 3); tele.TeleType = 30; }
                                    else if (number.StartsWith("019")) { number = "0109" + number.Remove(0, 3); tele.TeleType = 30; }
                                    else if (number.StartsWith("011")) { number = "0111" + number.Remove(0, 3); tele.TeleType = 30; }
                                    else if (number.StartsWith("014")) { number = "0114" + number.Remove(0, 3); tele.TeleType = 30; }
                                    else if (number.StartsWith("150")) { number = "0120" + number.Remove(0, 3); tele.TeleType = 30; }
                                    else if (number.StartsWith("151")) { number = "0101" + number.Remove(0, 3); tele.TeleType = 30; }
                                    else if (number.StartsWith("152")) { number = "0122" + number.Remove(0, 3); tele.TeleType = 30; }
                                }
                                else
                                    if (number.Length == 11)
                                    {
                                        if (number.StartsWith("0150")) { number = "0120" + number.Remove(0, 4); tele.TeleType = 30; }
                                        else if (number.StartsWith("0151")) { number = "0101" + number.Remove(0, 4); tele.TeleType = 30; }
                                        else if (number.StartsWith("0152")) { number = "0122" + number.Remove(0, 4); tele.TeleType = 30; }
                                    }

                            if (tele.TeleType == 0) tele.TeleType = 29;

                            tele.Number = number;

                            temp.Telephones.Add(tele);
                        }
                    }

                    // Courses
                    DataRow[] drCourses = ds_old.Tables[2].Select("AccountId = " + temp.AccountCorsatId);

                    temp.Courses = new List<Course>();

                    if (drCourses.Length > 0)
                    {


                        for (int j = 0; j < drCourses.Length; j++)
                        {
                            Course c = new Course();

                            if (drCourses[j]["CourseId"] != DBNull.Value)
                            {
                                MatchItem CourseMatchingResult = CoursesMatchingTable.Find(delegate(MatchItem mc) { return mc.OldId == Convert.ToInt32(drCourses[j]["CourseId"]); });
                                c.CourseId = CourseMatchingResult.NewId;
                            }

                            if (drCourses[j]["GradeId"] != DBNull.Value)
                            {
                                MatchItem GradeMatchingResult = GradesMatchingTable.Find(delegate(MatchItem mc) { return mc.OldId == Convert.ToInt32(drCourses[j]["GradeID"]); });
                                c.GradeId = GradeMatchingResult.NewId;
                            }
                            else
                                c.GradeId = null;

                            if (drCourses[j]["Degree"] != DBNull.Value)
                                c.Result = (float)Convert.ToDouble(drCourses[j]["Degree"]);
                            else
                                c.Result = null;

                            if (drCourses[j]["AccountIdInCourse"] != DBNull.Value)
                                c.AccountSerialInCourse = Convert.ToInt32(drCourses[j]["AccountIdInCourse"]);

                            temp.Courses.Add(c);
                        }

                    }

                    Accounts.Add(temp);


                    command_new.CommandText =
                        @"EXECUTE [SP_InsertAccount] 
                       @ArabicName = N'" + temp.FullName + @"'
                      ,@SSN = '" + temp.SSN + @"'
                      ,@IsOnlineRegistered = 1
                      ,@IsActivated = 1
                      ,@ChurchId = NULL
                      ,@DioceseId = NULL
                      ,@Email = ''
                      ,@BirthDate = '" + temp.BirthDate.ToString("yyyy-MM-dd") + @"'
                      ,@Year = 2011
                      ,@CorsatId = '" + temp.AccountCorsatId + @"'";

                    command_new.Parameters.Clear();
                    //command_new.Parameters.Add(new SqlParameter("ArabicName", temp.FullName));
                    //command_new.Parameters.Add(new SqlParameter("SSN", temp.SSN));
                    //command_new.Parameters.Add(new SqlParameter("IsOnlineRegistered", true));
                    //command_new.Parameters.Add(new SqlParameter("IsActivated", Convert.ToInt32(temp.IsActivated)));


                    //command_new.Parameters.Add(new SqlParameter("ChurchId", DBNull.Value));


                    //command_new.Parameters.Add(new SqlParameter("DioceseId", DBNull.Value));

                    //command_new.Parameters.Add(new SqlParameter("Email", ""));
                    //command_new.Parameters.Add(new SqlParameter("BirthDate", temp.BirthDate));
                    //command_new.Parameters.Add(new SqlParameter("Year", 2011));
                    //command_new.Parameters.Add(new SqlParameter("CorsatId", temp.AccountCorsatId));

                    command_new.CommandText +=
    @"

DECLARE @AccountId INT

SET @AccountId = (SELECT top(1) AccountsMaster.AccountId FROM AccountsMaster INNER JOIN AccountsDetails ON AccountsMaster.AccountId = AccountsDetails.AccountId WHERE AccountsMaster.SSN = '" + temp.SSN + @"' AND AccountsDetails.Year = 2011 )
 
";

                    for (int k = 0; k < temp.Telephones.Count; k++)
                        command_new.CommandText +=
                            @" 

if not exists (select * from AccountTelephones where AccountId = @AccountId AND Number = '" + temp.Telephones[k].Number + @"')
INSERT INTO AccountTelephones (AccountId, TeleTypeId, Number) 
VALUES (@AccountId, " + temp.Telephones[k].TeleType.ToString() + ",'" + temp.Telephones[k].Number + "')";

                    for (int k = 0; k < temp.Courses.Count; k++)
                        command_new.CommandText +=
    @" 

if not exists (select * from AccountCourses where AccountId = @AccountId AND CourseId = "
    + temp.Courses[k].CourseId.ToString()
    + @" AND [Year] = 2011)
INSERT INTO AccountCourses (AccountId, CourseId, [Year], AccountSerialInCourse, Result, GradeId) Values (@AccountId, "
    + temp.Courses[k].CourseId.ToString()
    + @",2011,"
    + temp.Courses[k].AccountSerialInCourse.ToString()
    + @","
    + (temp.Courses[k].Result.HasValue == false ? @"NULL" : temp.Courses[k].Result.Value.ToString()) + @","
    + (temp.Courses[k].GradeId.HasValue == false ? @"NULL" : temp.Courses[k].GradeId.Value.ToString()) + ")";


                    command_new.ExecuteNonQuery();



                }
                connection_new.Close();

                Console.WriteLine();
                Console.WriteLine("Finished Processing ... " + ds_old.Tables[0].Rows.Count.ToString() + " ... Done!");
                Console.WriteLine();
                Console.WriteLine("Press Any Key to Exit ... ");
                Console.ReadLine();


            //}
            //catch (Exception exc)
            //{ 
            //    connection_new.Close();
            //    Console.WriteLine();
            //    Console.WriteLine(exc.Message);
            //    Console.WriteLine();
            //    Console.WriteLine("An Error has occured !");
            //    Console.WriteLine();
            //    Console.WriteLine("Press Any Key to Exit ... ");
            //    Console.ReadLine();
            //}
        }
        */
        /*
        static void Main()
        {
            //string strConnString = @"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=;";

            //strConnString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Book1.xslx;Extended Properties=""Excel 12.0;HDR=No;IMEX=1""";

            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();

            Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Open(@"E:\Book1.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);

            Worksheet sheet = (Worksheet)wb.Sheets["Sheet1"];

            //RowIndex

            Microsoft.Office.Interop.Excel.Range workingRangeCells =
              sheet.get_Range("A1:N5047", Type.Missing);

            Array x = (System.Array)workingRangeCells.Cells.Value;
            //get_Value("A2");
            //string[] arrayS = ConvertToStringArray(array);

            string query = "";

            for (int i = 2; i < 5048; i++)
            {
                query += "UPDATE [AccountCourses] set ";
                query += "AccountSerialInCourse = " + x.GetValue(i, 4).ToString();
                query += " WHERE ";
                query += "AccountId = " + x.GetValue(i, 1).ToString();
                query += " AND ";
                query += "CourseId = " + x.GetValue(i, 2).ToString();
                query += " AND ";
                query += "Year = " + x.GetValue(i, 3).ToString();


                query += " \n\n ";

            }
            Console.WriteLine(query);
            Console.ReadKey();
        }
        */
        static void Main()
        {
            SqlConnection connection = new SqlConnection("Server=.; Initial Catalog=jmy_test; integrated security=true;");
            SqlCommand command = new SqlCommand("Select CourseId, Year, DayId from CoursesDetails", connection);

            SqlDataAdapter adapter = new SqlDataAdapter(command);

            DataTable dtResults = new System.Data.DataTable();

            adapter.Fill(dtResults);

            string query = "";

            for (int i = 0; i < dtResults.Rows.Count; i++)
            {
                DataRow drCourseDetail = dtResults.Rows[i];

                int courseId = Convert.ToInt32(drCourseDetail["CourseId"]);
                int dayId = Convert.ToInt32(drCourseDetail["DayId"]);

                query += "INSERT INTO CoursesDetails (CourseId, Year, DayId) values (" + courseId.ToString() + ",2013," + dayId.ToString() + ")\n";
            }

        }

        static string[] ConvertToStringArray(System.Array values)
        {

            // create a new string array
            string[] theArray = new string[values.Length];

            // loop through the 2-D System.Array and populate the 1-D String Array
            for (int i = 1; i <= values.Length; i++)
            {
                if (values.GetValue(1, i) == null)
                    theArray[i - 1] = "";
                else
                    theArray[i - 1] = (string)values.GetValue(1, i).ToString();
            }

            return theArray;
        }
    }


    struct Account
    {
        public string AccountCorsatId;
        public string SSN;
        public string FullName;
        public DateTime BirthDate;
        public bool IsActivated;
        public string Email;

        public List<Telephone> Telephones;

        public List<Course> Courses;
    }

    struct Course
    {
        public int AccountId;
        public int CourseId;
        public int AccountSerialInCourse;
        public float? Result;
        public int? GradeId;
    }

    struct MatchItem
    {
        public int OldId;
        public int NewId;

        public MatchItem(int oldId, int newId)
        {
            OldId = oldId;
            NewId = newId;
        }
    }

    struct Telephone
    {
        public int TeleType;
        public string Number;
    }

}
